from CourseAssistant import constant
from CourseAssistant.sql import sqlexecute


# Create your models here.

# sqlexecute(self, type, tablename, values=None, where=None, whereand=True)


def register(username, userid, password):
    where = {'user_id': userid}
    data, effect_row = sqlexecute('s', constant.teacherTable, where=where)
    if effect_row != 0:
        return False
    values = [username, userid, password]
    effect_row, mid = sqlexecute('i', constant.teacherTable, values=values)
    return effect_row == 0


def login(userid, password):
    where = {'user_id': userid}
    field = ['password', 'user_name', 'id']
    data, effect_row = sqlexecute('s', constant.teacherTable, values=field, where=where)
    if effect_row == 1 and data[0][0] == password:
        return True, data[0][1], data[0][2]  # ????
    return False, 0, 0


def getCourseList(teacher_id):
    fields = ['id', 'course_name']
    where = {'teacher_id': teacher_id}
    data, erow = sqlexecute('s', constant.courseTable, values=fields, where=where)
    if erow == 0:
        return False, -1
    res = []
    for course in data:
        temp = {'id': course[0], 'course_name': course[1]}
        res.append(temp)
    return True, res


def addCourse(coursename, teacherid, collageid):
    fields = [coursename, teacherid, 0, 0, 0, 0, collageid]
    erow = sqlexecute('i', constant.courseTable, values=fields)
    return erow != 0


def addSingle(studentid, studentname, courseid, absent_num, homework_score, mid_score, final_score):
    where = {
        'student_id': studentid,
        'course_id': courseid
    }
    data, erow= sqlexecute('s', constant.student_courseTable, where=where)
    if erow != 0:
        values = {
                    'student_name': studentname,
                    'absent_num': absent_num,
                    'homework_score': homework_score,
                    'mid_score': mid_score,
                    'final_score': final_score
                  }
        erow, mid = sqlexecute('u', constant.student_courseTable,values=values, where=where)
    else:
        fields = [studentid, studentname, courseid, mid_score, final_score, homework_score, absent_num, 0]
        erow, mid = sqlexecute('i', constant.student_courseTable, values=fields)
    return erow != 0


def delSingle(studentid, courseid):
    where = {'student_id': studentid, 'course_id': courseid}
    erow = sqlexecute('d', constant.student_courseTable, where=where)
    return erow != 0


def addExl(data):
    flag = True
    for student in data:
        flag = addSingle(student[0], student[1], student[2])
        if not flag:
            return False
    return True


def getCalScore(courseid):
    fields = ['mid_weight', 'final_weight', 'homework_weight', 'absent_score']
    where = {'id': courseid}
    data, erow = sqlexecute('s', constant.courseTable, values=fields, where=where)
    return data


def calScore(courseid, mid_weight, final_weight, homework_weight, absent_score):
    where = {'id': courseid}
    values = {'mid_weight': mid_weight, 'final_weight': final_weight, 'homework_weight': homework_weight,
              'absent_score': absent_score}
    flag = sqlexecute('u', constant.courseTable, values=values, where=where)
    if not flag:
        return False
    return True


def getStudent(courseid):
    fields = ['student_id', 'student_name', 'mid_score', 'final_score', 'homework_score', 'absent_num', 'score']
    where = {'course_id': courseid}
    data, erow = sqlexecute('s', constant.student_courseTable, values=fields, where=where)
    return data, erow != 0


def calculation(courseid):
    where = {'id': courseid}
    field = ['mid_weight', 'final_weight', 'homework_weight', 'absent_score']
    data, erow = sqlexecute('s', constant.courseTable, values=field, where=where)
    where = {'course_id': courseid}
    mid_weight = data[0][0]
    final_weight = data[0][1]
    homework_weight = data[0][2]
    absent_score = data[0][3]
    sum = mid_weight + final_weight + homework_weight
    fields = ['id', 'mid_score', 'final_score', 'homework_score', 'absent_num']
    datas, erow = sqlexecute('s', constant.student_courseTable, values=fields, where=where)
    print(datas)
    for data in datas:
        mid = data[0]
        mid_score = int(data[1])
        final_score = int(data[2])
        homework_score = int(data[3])
        absent_num = int(data[4])
        score = mid_weight * 1.0 / sum * mid_score + final_weight * 1.0 / sum * final_score + homework_weight * 1.0 / sum * homework_score - absent_num * absent_score
        print(score)
        if score < 0:
            score = 0
        score = int(score)
        score = str(score)
        fields = {'score': score}
        where = {'id': mid}
        erow, idd = sqlexecute('u', constant.student_courseTable, values=fields, where=where)
    return True